package com.xiyu.service.service.infra.codegen;

import com.xiyu.service.convert.infra.codegen.DatabaseTableConvert;
import com.xiyu.service.vo.infra.codegen.databaseTable.DatabaseTableGetColumnByNamesOutput;
import com.xiyu.service.vo.infra.codegen.databaseTable.DatabaseTableGetColumnByNamesInput;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import com.xiyu.service.convert.infra.codegen.CodegenConvert;
import com.xiyu.service.model.infra.codegen.*;
import com.xiyu.service.repository.infra.codegen.*;
import com.xiyu.service.service.infra.codegen.inner.CodegenEngine;
import com.xiyu.service.vo.infra.codegen.baseVO.InfraDatabaseColumnBase;
import com.xiyu.service.vo.infra.codegen.database.*;
import org.babyfish.jimmer.sql.ast.mutation.DeleteMode;
import org.springframework.stereotype.Service;
import jakarta.annotation.Resource;
import org.springframework.validation.annotation.Validated;
import org.babyfish.jimmer.Page;
import java.util.*;

import org.springframework.transaction.annotation.Transactional;

import java.util.stream.Collectors;

import com.xiyu.service.util.entity.EntityUtils;
import com.xiyu.service.framework.web.web.core.pojo.PageResult;

import static cn.hutool.core.text.CharSequenceUtil.toCamelCase;
import static cn.hutool.core.text.CharSequenceUtil.upperFirst;
import static cn.hutool.core.text.NamingCase.toSymbolCase;
import static com.xiyu.service.errorCode.infra.ErrorCodeConstants.*;
import static com.xiyu.service.framework.exception.util.ServiceExceptionUtil.exception;

/**
 * 数据库表 Service 实现类
 */
@Service
@Validated
public class DatabaseTableServiceImpl implements DatabaseTableService {

    @Resource
    private InfraDatabaseTableRepository infraDatabaseTableRepository;

    @Resource
    private InfraDatabaseColumnRepository infraDatabaseColumnRepository;

    @Resource
    private InfraDatabaseIndexRepository infraDatabaseIndexRepository;

    @Resource
    private InfraInterfaceValidationRepository infraInterfaceValidationRepository;

    @Resource
    private InfraInterfaceVoClassRepository infraInterfaceVoClassRepository;

    @Resource
    private InfraDatabaseMappingRepository infraDatabaseMappingRepository;

    @Resource
    private CodegenEngine codegenEngine;

    @Override
    public PageResult<DatabaseTableResp> getDatabaseTableList(DatabaseTableListReqVO list) {
        Page<InfraDatabaseTable> tables = infraDatabaseTableRepository.selectList(list);
        List<DatabaseTableResp> databaseTableReps = CodegenConvert.INSTANCE.convertList05(tables.getRows());
        return new PageResult<>(databaseTableReps, tables.getTotalRowCount());
    }

    @Override
    public List<DatabaseTableColumnResp> getColumnList(DatabaseTableListReqVO list) {
        list.setName(toSymbolCase(list.getName(), '_'));
        List<InfraDatabaseTable> tables = infraDatabaseTableRepository.selectColumnList(list);
        return CodegenConvert.INSTANCE.convertList13(tables);
    }

    @Override
    public List<DatabaseTableResp> getDatabaseTableName(String name) {
        if(name.isEmpty())
            return null;
        else
            return CodegenConvert.INSTANCE.convertList05(infraDatabaseTableRepository.selectName(name));
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public UUID createTable(DatabaseUpdateReq reqVo) {
        Optional<InfraDatabaseTable> opExistsTable = infraDatabaseTableRepository.findByName(reqVo.getName());
        if (opExistsTable.isPresent())
            throw exception(CODEGEN_DATABASE_TABLE_EXISTS);
        // 判断表字段是否重复
        List<String> columnNameList = reqVo.getColumns().stream().map(InfraDatabaseColumnBase::getColumnName).collect(Collectors.toList());
        List<String> distinctColumnNameList = CollectionUtil.distinct(columnNameList);
        if(distinctColumnNameList.size() != columnNameList.size())
            throw exception(CODEGEN_DATABASE_TABLE_COLUMN_DISTINCT);

        // 保存数据库表字段校验
        for(DatabaseUpdateReq.Column column : reqVo.getColumns()){
            if(!column.getValidations().isEmpty()){
                List<InfraInterfaceValidation> validations = CodegenConvert.INSTANCE.convertList17(column.getValidations());
                infraInterfaceValidationRepository.saveAll(validations);
                validations.forEach(validation -> codegenEngine.saveInsertSql(validation));
            }
            column.setValidations(Collections.emptyList());
        }
        InfraDatabaseTable newDatabaseTable = CodegenConvert.INSTANCE.convert(reqVo);

        // 保存数据库表
        codegenEngine.saveInsertSql(newDatabaseTable);
        newDatabaseTable.columns().forEach(column -> codegenEngine.saveInsertSql(column));
        newDatabaseTable.indexes().forEach(index -> codegenEngine.saveInsertSql(index));
        newDatabaseTable.mappings().forEach(mapping -> codegenEngine.saveInsertSql(mapping));
        newDatabaseTable = infraDatabaseTableRepository.insert(newDatabaseTable);


        // 保存数据库表对应的接口参数类
        InfraDatabaseTable finalNewDatabaseTable = newDatabaseTable;
        InfraInterfaceVoClass newParamClass = InfraInterfaceVoClassDraft.$.produce(draft -> {
            String simpleClassName = upperFirst(toCamelCase((reqVo.getName())));
            draft.setName(upperFirst(StrUtil.toCamelCase(simpleClassName)) + "Base")
                    .setParentId(finalNewDatabaseTable.id().toString())
                    .setComment(reqVo.getComment());
        });
        newParamClass = infraInterfaceVoClassRepository.insert(newParamClass);
        codegenEngine.saveInsertSql(newParamClass);
        // 重新获取完整的table信息
        newDatabaseTable = infraDatabaseTableRepository.findDetailById(newDatabaseTable.id()).get();
        codegenEngine.tableInsertExecute(newDatabaseTable);
        return newDatabaseTable.id();
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public UUID updateTable(DatabaseUpdateReq reqVo){
        Optional<InfraDatabaseTable> tableOptional = infraDatabaseTableRepository.findDetailById(reqVo.getId());
        if (tableOptional.isEmpty())
            throw exception(CODEGEN_DATABASE_TABLE_NOT_EXISTS);
        List<DatabaseUpdateReq.Column> reqVoColumnList = reqVo.getColumns();
        StringBuilder updateSql = new StringBuilder("ALTER TABLE " + tableOptional.get().name() + "\n");

        // 更新column
        for(DatabaseUpdateReq.Column reqVoColumn : reqVoColumnList){
            if(Objects.equals(reqVoColumn.getOperateType(), "delete")){
                updateSql.append(deleteColumn(reqVoColumn));
            }else if(Objects.equals(reqVoColumn.getOperateType(), "new")){
                updateSql.append(newColumn(reqVoColumn));
            } else {
                updateSql.append(updateColumn(reqVoColumn));
            }
        }
        // 更新index
        List<DatabaseUpdateReq.Index> reqVoIndexList = reqVo.getIndexes();
        for(DatabaseUpdateReq.Index reqVoIndex : reqVoIndexList){
            if(Objects.equals(reqVoIndex.getOperateType(), "delete")){
                updateSql.append(deleteIndex(reqVoIndex));
            }else if(Objects.equals(reqVoIndex.getOperateType(), "new")){
                updateSql.append(newIndex(reqVoIndex, reqVo.getId()));
            }else{
                updateSql.append(updateIndex(reqVoIndex, reqVo.getId()));
            }
        }

        List<DatabaseUpdateReq.mapping> reqVoMappingList = reqVo.getMappings();
        for(DatabaseUpdateReq.mapping reqVoMapping : reqVoMappingList){
            if(Objects.equals(reqVoMapping.getOperateType(), "delete")){
                infraDatabaseMappingRepository.deleteById(reqVoMapping.getId(), DeleteMode.PHYSICAL);
                codegenEngine.saveDeleteSql(InfraDatabaseMapping.class.getName(), reqVoMapping.getId().toString());
            }else if(Objects.equals(reqVoMapping.getOperateType(), "new")){
                InfraDatabaseMapping newMapping = CodegenConvert.INSTANCE.convert(reqVoMapping);
                newMapping = InfraDatabaseMappingDraft.$.produce(newMapping, draft -> draft.setTableId(reqVo.getId()));
                newMapping = infraDatabaseMappingRepository.insert(newMapping);
                codegenEngine.saveInsertSql(newMapping);
            }else{
                InfraDatabaseMapping updateMapping = CodegenConvert.INSTANCE.convert(reqVoMapping);
                updateMapping = InfraDatabaseMappingDraft.$.produce(updateMapping, draft -> draft.setTableId(reqVo.getId()));
                Optional<InfraDatabaseMapping> opOldMapping = infraDatabaseMappingRepository.findById(reqVoMapping.getId());
                if (opOldMapping.isEmpty())
                    throw exception(CODEGEN_DATABASE_MAPPING_NOT_EXITS);
                if (!EntityUtils.isEquals(opOldMapping.get(), updateMapping)){
                    infraDatabaseMappingRepository.update(updateMapping);
                    codegenEngine.saveUpdateSql(updateMapping);
                }

            }
        }

        // 更新table
        if(!Objects.equals(tableOptional.get().name(), reqVo.getName())
                || !Objects.equals(tableOptional.get().firstModule(), reqVo.getFirstModule())
                || !Objects.equals(tableOptional.get().secondModule(), reqVo.getSecondModule())
                || !Objects.equals(tableOptional.get().remark(), reqVo.getRemark())
                || !Objects.equals(tableOptional.get().comment(), reqVo.getComment())){
            InfraDatabaseTable updateDatabaseTable = InfraDatabaseTableDraft.$.produce(draft -> draft.setName(reqVo.getName()).setFirstModule(reqVo.getFirstModule()).setSecondModule(reqVo.getSecondModule()).setComment(reqVo.getComment()).setRemark(reqVo.getRemark()).setId(reqVo.getId()));
            infraDatabaseTableRepository.updateById(reqVo.getId(), updateDatabaseTable);
            codegenEngine.saveUpdateSql(updateDatabaseTable);
            // 更新VoClass
            Optional<InfraInterfaceVoClass>  opUpdateVoClass = infraInterfaceVoClassRepository.findFirstByParentId(reqVo.getId().toString());
            if(opUpdateVoClass.isPresent()) {
                InfraInterfaceVoClass updateVoClass = InfraInterfaceVoClassDraft.$.produce(opUpdateVoClass.get(), draft -> draft.setComment(reqVo.getComment())
                        .setName(upperFirst(StrUtil.toCamelCase(reqVo.getName())) + "Base"));
                infraInterfaceVoClassRepository.update(updateVoClass);
                codegenEngine.saveUpdateSql(updateVoClass);
            }
        }
        // 将最后一个，改成；
        if(updateSql.lastIndexOf(",") > 0)
            updateSql.replace(updateSql.lastIndexOf(","), updateSql.lastIndexOf(",") + 1, ";") ;
        else
            updateSql = new StringBuilder();

        if(!Objects.equals(tableOptional.get().name(), reqVo.getName()))
            updateSql.append("ALTER TABLE ").append(tableOptional.get().name())
                    .append(" RENAME TO ").append(reqVo.getName()).append(";\n");

        // 执行代码生成
        InfraDatabaseTable updateDatabaseTable = infraDatabaseTableRepository.findDetailById(reqVo.getId()).get();
        codegenEngine.tableUpdateExecute(updateDatabaseTable, tableOptional.get(), updateSql.toString());
        return tableOptional.get().id();
    }

    private String deleteColumn(DatabaseUpdateReq.Column reqVoColumn){
        Optional<InfraDatabaseColumn> optionalColumn = infraDatabaseColumnRepository.findById(reqVoColumn.getId());
        if(optionalColumn.isEmpty())
            return "";
        // 删除校验
        List<InfraInterfaceValidation> validationList = infraInterfaceValidationRepository.findByParentId(reqVoColumn.getId());
        for(InfraInterfaceValidation validation : validationList){
            infraInterfaceValidationRepository.deleteById(validation.id(), DeleteMode.PHYSICAL);
            codegenEngine.saveDeleteSql(InfraInterfaceValidation.class.getName(), validation.id().toString());
        }

        // 删除字段
        infraDatabaseColumnRepository.deleteById(reqVoColumn.getId(), DeleteMode.PHYSICAL);
        codegenEngine.saveDeleteSql(InfraDatabaseColumn.class.getName(), reqVoColumn.getId().toString());
        return "DROP COLUMN `" + reqVoColumn.getColumnName() + "`,\n";
    }

    private String newColumn(DatabaseUpdateReq.Column reqVoColumn){
        List<InfraInterfaceValidation> validations = CodegenConvert.INSTANCE.convertList17(reqVoColumn.getValidations());
        infraInterfaceValidationRepository.saveAll(validations);
        validations.forEach(validation -> codegenEngine.saveInsertSql(validation));
        reqVoColumn.setValidations(Collections.emptyList());
        InfraDatabaseColumn newColumn = CodegenConvert.INSTANCE.convert(reqVoColumn);
        infraDatabaseColumnRepository.insert(newColumn);
        codegenEngine.saveInsertSql(newColumn);
        return "ADD COLUMN `" + newColumn.columnName() + "`"
                + " " + newColumn.dataType()
                + " " + (newColumn.nullable() ? "": "NOT NULL")
                + " " + (Objects.equals(newColumn.defaultValue(), "") ? "" : ("DEFAULT " + newColumn.defaultValue()))
                + " " + (Objects.equals(newColumn.columnComment(), "") ? "" : ("COMMENT '" + newColumn.columnComment() + "'"))
                + ",\n";
    }

    private String updateColumn(DatabaseUpdateReq.Column reqVoColumn){
        if (Objects.equals(reqVoColumn.getColumnName(), "create_time")
                || Objects.equals(reqVoColumn.getColumnName(), "update_time") || Objects.equals(reqVoColumn.getColumnName(), "creator_id")
                || Objects.equals(reqVoColumn.getColumnName(), "updater_id") || Objects.equals(reqVoColumn.getColumnName(), "deletedTime"))
            return "";
        if(reqVoColumn.getValidations() != null) {
            for(DatabaseUpdateReq.Validation validation : reqVoColumn.getValidations())
            {
                if (Objects.equals(validation.getOperateType(), "new")) {
                    InfraInterfaceValidation newValidation = CodegenConvert.INSTANCE.convert(validation);
                    newValidation = InfraInterfaceValidationDraft.$.produce(newValidation, draft -> draft.setParentId(reqVoColumn.getId()));
                    infraInterfaceValidationRepository.insert(newValidation);
                    codegenEngine.saveInsertSql(newValidation);
                }
                else if (Objects.equals(validation.getOperateType(), "delete")) {
                    infraInterfaceValidationRepository.deleteById(validation.getId(), DeleteMode.PHYSICAL);
                    codegenEngine.saveDeleteSql(InfraInterfaceValidation.class.getName(), validation.getId().toString());
                }else {
                    InfraInterfaceValidation oldValidation = infraInterfaceValidationRepository.findById(validation.getId()).get();
                    if(!Objects.equals(oldValidation.validation(), validation.getValidation())
                            || !Objects.equals(oldValidation.validationCondition(), validation.getValidationCondition())
                            || !Objects.equals(oldValidation.message(), validation.getMessage())) {
                        InfraInterfaceValidation updateValidation = CodegenConvert.INSTANCE.convert(validation);
                        updateValidation = InfraInterfaceValidationDraft.$.produce(updateValidation, draft -> draft.setParentId(reqVoColumn.getId()));
                        infraInterfaceValidationRepository.update(updateValidation);
                        codegenEngine.saveUpdateSql(updateValidation);
                    }
                }
            }
            reqVoColumn.setValidations(Collections.emptyList());
        }

        boolean isColumnChange = false;
        InfraDatabaseColumn updateColumn = CodegenConvert.INSTANCE.convert(reqVoColumn);
        InfraDatabaseColumn oldColumn = infraDatabaseColumnRepository.findById(reqVoColumn.getId()).get();
        if(!Objects.equals(oldColumn.columnName(), reqVoColumn.getColumnName())
                ||!Objects.equals(oldColumn.columnComment(), reqVoColumn.getColumnComment())
                ||!Objects.equals(oldColumn.dataType(), reqVoColumn.getDataType())
                ||!Objects.equals(oldColumn.defaultValue(), reqVoColumn.getDefaultValue())
                ||!Objects.equals(oldColumn.nullable(), reqVoColumn.getNullable())
                ||!Objects.equals(oldColumn.dictType(), reqVoColumn.getDictType())
                ||!Objects.equals(oldColumn.example(), reqVoColumn.getExample())
                ||!Objects.equals(oldColumn.javaType(), reqVoColumn.getJavaType())
                ||!Objects.equals(oldColumn.required(), reqVoColumn.getRequired())
                ||!Objects.equals(oldColumn.relatedTable(), reqVoColumn.getRelatedTable())
                ||!Objects.equals(oldColumn.relatedTableType(), reqVoColumn.getRelatedTableType())

        ) {
            isColumnChange = true;
            infraDatabaseColumnRepository.updateColumn(updateColumn);
            codegenEngine.saveUpdateSql(updateColumn);
        }


        if (isColumnChange)
            return "CHANGE `" + oldColumn.columnName() + "`"
                    + " " + "`" + updateColumn.columnName() + "`"
                    + " " + updateColumn.dataType()
                    + " " + (updateColumn.nullable() ? "": "NOT NULL")
                    + " " + (Objects.equals(updateColumn.defaultValue(), "") ? "" : ("DEFAULT " + updateColumn.defaultValue()))
                    + " " + (Objects.equals(updateColumn.columnComment(), "") ? "" : ("COMMENT '" + updateColumn.columnComment() + "'"))
                    + ",\n";
        else
            return "";
    }

    private String deleteIndex(DatabaseUpdateReq.Index reqVoIndex){
        infraDatabaseIndexRepository.deleteById(reqVoIndex.getId(), DeleteMode.PHYSICAL);
        codegenEngine.saveDeleteSql(InfraDatabaseIndex.class.getName(), reqVoIndex.getId().toString());
        return "DROP INDEX `" + reqVoIndex.getIndexName() + "`,\n";
    }

    private String newIndex(DatabaseUpdateReq.Index reqVoIndex, UUID tableId){
        InfraDatabaseIndex newIndex = CodegenConvert.INSTANCE.convert(reqVoIndex);
        newIndex = InfraDatabaseIndexDraft.$.produce(newIndex, draft -> draft.setTableId(tableId));
        if (isExistIllegalColumn(tableId, reqVoIndex))
            return "";

        infraDatabaseIndexRepository.insert(newIndex);
        codegenEngine.saveInsertSql(newIndex);
        return "ADD " + reqVoIndex.getIndexType()
                + " " +" `" + reqVoIndex.getIndexName() + "`"
                + " " + "(" + reqVoIndex.getColumnNames().stream().map((name) -> "`" + name + "`").collect(Collectors.joining(","))+ ")"
                + ",\n";
    }

    private boolean isExistIllegalColumn(UUID tableId, DatabaseUpdateReq.Index reqVoIndex){
        int existsCount = infraDatabaseColumnRepository.countByTableIdAndColumnNameIn(tableId, reqVoIndex.getColumnNames());
        return existsCount != reqVoIndex.getColumnNames().size();
    }

    private String updateIndex(DatabaseUpdateReq.Index reqVoIndex, UUID tableId){
        InfraDatabaseIndex oldIndex = infraDatabaseIndexRepository.findById(reqVoIndex.getId()).get();
        if (Objects.equals(oldIndex.indexName(), reqVoIndex.getIndexName())
                && Objects.equals(oldIndex.indexType(), reqVoIndex.getIndexType())
                && Objects.equals(oldIndex.columnNames().toString(), reqVoIndex.getColumnNames().toString()))
        {
            return "";
        }
        if (isExistIllegalColumn(tableId, reqVoIndex))
            return "";
        InfraDatabaseIndex updateIndex = CodegenConvert.INSTANCE.convert(reqVoIndex);
        updateIndex = InfraDatabaseIndexDraft.$.produce(updateIndex, draft -> draft.setTableId(tableId));
        infraDatabaseIndexRepository.update(updateIndex);
        codegenEngine.saveUpdateSql(updateIndex);
        return "DROP INDEX `" + oldIndex.indexName() + "`,\n"
                + "ADD " + reqVoIndex.getIndexType()
                + " " +" `" + reqVoIndex.getIndexName() + "`"
                + " " + "(" + reqVoIndex.getColumnNames().stream().map((name) -> "`" + name + "`").collect(Collectors.joining(","))+ ")"
                + ",\n";
    }



    @Override
    public DatabaseTableDetailResp getDatabaseTable(UUID tableId){
        Optional<InfraDatabaseTable> infraDatabaseTableOptional = infraDatabaseTableRepository.findDetailById(tableId);
        DatabaseTableDetailResp detailRespVo = new DatabaseTableDetailResp();
        if(infraDatabaseTableOptional.isPresent()) {
            detailRespVo = CodegenConvert.INSTANCE.convert(infraDatabaseTableOptional.get());
        }
        return detailRespVo;
    }

    @Override
    public void deleted(String id){
        UUID tableId = UUID.fromString(id);
        InfraDatabaseTable deleteTable = infraDatabaseTableRepository.findDetailById(tableId).get();
        codegenEngine.tableDeleteExecute(deleteTable, "DROP TABLE IF EXISTS "+deleteTable.name() + ";\n");
        deletedTableSaveDeleteSql(tableId);
        infraDatabaseColumnRepository.deleteByTableId(tableId);
        infraDatabaseIndexRepository.deleteByTableId(tableId);
        infraDatabaseMappingRepository.deleteByTableId(tableId);
        infraInterfaceVoClassRepository.deleteByParentId(id);
        infraDatabaseTableRepository.deleteById(tableId, DeleteMode.PHYSICAL);
    }

    private void deletedTableSaveDeleteSql(UUID tableId){
        List<InfraDatabaseColumn> columnList = infraDatabaseColumnRepository.findByTableId(tableId);
        columnList.forEach(column -> codegenEngine.saveDeleteSql(InfraDatabaseColumn.class.getName(), column.id().toString()));

        List<InfraDatabaseIndex> indexList = infraDatabaseIndexRepository.findByTableId(tableId);
        indexList.forEach(index -> codegenEngine.saveDeleteSql(InfraDatabaseIndex.class.getName(), index.id().toString()));

        List<InfraDatabaseMapping> mappingList = infraDatabaseMappingRepository.findByTableId(tableId);
        mappingList.forEach(mapping -> codegenEngine.saveDeleteSql(InfraDatabaseMapping.class.getName(), mapping.id().toString()));

        List<InfraInterfaceVoClass> voClassList = infraInterfaceVoClassRepository.findByParentId(tableId.toString());
        voClassList.forEach(voClass -> codegenEngine.saveDeleteSql(InfraInterfaceVoClass.class.getName(), voClass.id().toString()));

        codegenEngine.saveDeleteSql(InfraDatabaseTable.class.getName(), tableId.toString());
    }
    @Override
    public List<DatabaseTableGetColumnByNamesOutput> getColumnByNames(DatabaseTableGetColumnByNamesInput inputVO) {
        return DatabaseTableConvert.INSTANCE.getColumnByNamesOutputConvert(infraDatabaseColumnRepository.findByTableNameAndColumnName(inputVO.getTableName(), inputVO.getColumnNames()));
    }

}
